﻿using QuanLyGiaoVuDAL.ADOWrapper;
using QuanLyGiaoVuDAL.DTO;
using QuanLyGiaoVuDAL.Mapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class DeTaiRepository:IDeTaiRepository
    {
        public DeTaiDTO InsertDeTai(DeTaiDTO deTaiDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DeTaiInsert]", GetParameterArray(deTaiDTO));
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToDeTaiDTO2();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public DeTaiDTO UpdateDeTai(DeTaiDTO deTaiDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DeTaiUpdate]", GetParameterArray(deTaiDTO));
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToDeTaiDTO2();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public bool DeleteDeTai(Guid maDeTai)
        {            
            try
            {
                var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DeTaiDelete]", DBParameterFactory.Create("@MaDeTai", maDeTai));
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public DeTaiDTO GetDeTaiById(Guid maDeTai)
        {
            SqlParameter[] parameters = new SqlParameter[5];
            parameters[0] = DBParameterFactory.Create("MaDeTai", maDeTai);
            parameters[1] = DBParameterFactory.Create("MaGiangVien1", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[2] = DBParameterFactory.Create("MaGiangVien2", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[3] = DBParameterFactory.Create("MaHocKy", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[4] = DBParameterFactory.Create("MaNamHoc", SqlDbType.UniqueIdentifier, DBNull.Value);

            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DeTaiSelect]", parameters);
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToDeTaiDTO2();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public IEnumerable<DeTaiDTO> GetAllDeTai(Guid? maGiangVien1, Guid? maGiangVien2, Guid? maHocKy, Guid? maNamHoc, bool? daDuyet, bool? datYeuCau)
        {
            List<DeTaiDTO> deTaiList = new List<DeTaiDTO>();
            SqlParameter[] parameters = new SqlParameter[7];
            parameters[0] = DBParameterFactory.Create("MaDeTai", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[1] = maGiangVien1 != null ? DBParameterFactory.Create("MaGiangVien1", (Guid)maGiangVien1) : DBParameterFactory.Create("MaGiangVien1", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[2] = maGiangVien2 != null ? DBParameterFactory.Create("MaGiangVien2", (Guid)maGiangVien2) : DBParameterFactory.Create("MaGiangVien2", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[3] = maHocKy != null ? DBParameterFactory.Create("MaHocKy", (Guid)maHocKy) : DBParameterFactory.Create("MaHocKy", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[4] = maNamHoc != null ? DBParameterFactory.Create("MaNamHoc", maNamHoc) : DBParameterFactory.Create("MaNamHoc", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[5] = daDuyet != null ? DBParameterFactory.Create("DaDuyet", daDuyet) : DBParameterFactory.Create("DaDuyet", SqlDbType.Bit, DBNull.Value);
            parameters[6] = datYeuCau != null ? DBParameterFactory.Create("DatYeuCau", datYeuCau) : DBParameterFactory.Create("DatYeuCau", SqlDbType.Bit, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DeTaiSelect]", parameters );
            try
            {
                var detaiTable = dbResult.Tables[0].Rows;
                foreach (DataRow dataRow in detaiTable)
                {
                    deTaiList.Add(dataRow.ToDeTaiDTO2());
                }
                return deTaiList.AsEnumerable();
            }
            catch (Exception ex)
            {
                return null;
            }                        
        }
        public IEnumerable<DeTaiDTO> GetAllDeTaiNamHoc(Guid maNamHoc)
        {
            List<DeTaiDTO> deTaiList = new List<DeTaiDTO>();
            SqlParameter[] parameters = new SqlParameter[5];
            parameters[0] = DBParameterFactory.Create("MaDeTai", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[1] = DBParameterFactory.Create("MaGiangVien1", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[2] = DBParameterFactory.Create("MaGiangVien2", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[3] = DBParameterFactory.Create("MaHocKy", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[4] = DBParameterFactory.Create("MaNamHoc", SqlDbType.UniqueIdentifier, maNamHoc);

            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DeTaiSelect]", parameters);
            try
            {
                var deTaiTable = dbResult.Tables[0].Rows;
                foreach (DataRow dataRow in deTaiTable)
                {
                    deTaiList.Add(dataRow.ToDeTaiDTO2());
                }
                return deTaiList.AsEnumerable();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public IEnumerable<DeTaiDTO> GetAllDeTaiGiangVien(Guid giangVienHD1, Guid maHocKy,bool? daDuyet)
        {
            List<DeTaiDTO> deTaiList = new List<DeTaiDTO>();
            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = DBParameterFactory.Create("GiangVienHD1", SqlDbType.UniqueIdentifier, giangVienHD1);
            parameters[1] = DBParameterFactory.Create("MaHocKy", SqlDbType.UniqueIdentifier, maHocKy);
            parameters[2] = DBParameterFactory.Create("DatYeuCau", daDuyet);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DeTaiGiangVienSelect]", parameters);
            try
            {
                var caTable = dbResult.Tables[0].Rows;
                foreach (DataRow dataRow in caTable)
                {
                    deTaiList.Add(dataRow.ToDeTaiDTO2());
                }
                return deTaiList.AsEnumerable();
            }
            catch (Exception ex)
            {
                return null;
            }  
        }
        #region Private Methods
        private System.Data.SqlClient.SqlParameter[] GetParameterArray(DeTaiDTO deTaiDTO)
        {
            SqlParameter[] parameters = new SqlParameter[11];
            parameters[0] = DBParameterFactory.Create("MaDeTai", deTaiDTO.MaDeTai);
            parameters[1] = DBParameterFactory.Create("TenDeTai", deTaiDTO.TenDeTai);
            parameters[2] = DBParameterFactory.Create("GiangVienHD1", deTaiDTO.GiangVienHD1);            
            parameters[3] = DBParameterFactory.Create("GiangVienHD2", deTaiDTO.GiangVienHD2);
            parameters[4] = DBParameterFactory.Create("KhoaLuanTotNghiep", deTaiDTO.KhoaLuanTotNghiep);
            parameters[5] = DBParameterFactory.Create("MaHocKy", deTaiDTO.MaHocKy);
            parameters[6] = DBParameterFactory.Create("MoTa", deTaiDTO.MoTa);
            parameters[7] = DBParameterFactory.Create("DaDuyet", deTaiDTO.DaDuyet);
            parameters[8] = DBParameterFactory.Create("DatYeuCau", deTaiDTO.DatYeuCau);
            parameters[9] = DBParameterFactory.Create("GhiChu", deTaiDTO.GhiChu ?? string.Empty);
            parameters[10] = DBParameterFactory.Create("FileTaiLieu", deTaiDTO.FileTaiLieu);

            return parameters;
        }
        #endregion
    }
}
